if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_import_overTime')
          and type in ('P','PC'))
   drop procedure bd_proc_import_overTime
go


create procedure bd_proc_import_overTime (@serialNumber varchar(20),@operator varchar(20)) as
BEGIN
	--名称：导入加班保存
	--思路：
			--1、从bd_temp_import_overTime表中取出对应的数据，
			--2过滤不符合条件人员从#temp_data移除，#return_data中添加
			--3同部门、同一天的为一个加班单，根据部门、日期遍历
			--3.1 将根据部门、日期为条件查询 的数据放入#temp_order_data，不为空，则可以生产加班单和明细单，并记录id
			--4返回数据，将#return_data转json字符串，通过select 返回其字符串和生成的单子id字符串（逗号隔开）
	DECLARE @dep_serial varchar(50)      --部门id
	DECLARE @date date 					--加班日期
	DECLARE @i int 					--加班日期

	DECLARE @counts int = 0 --判断返回条数，公共。

  DECLARE @order_id int --加班单ID
	DECLARE @order_no varchar(20) --加班单编号

	DECLARE @order_ids varchar(max)='' --加班单编号


	--加班明细临时表
	create table #temp_data(
		user_serial bigint,--人员ID
		user_no varchar(50) COLLATE Chinese_PRC_CI_AS,--工号
		user_name varchar(100) COLLATE Chinese_PRC_CI_AS,--人员姓名
		user_dep bigint,--部门id
		dep_name varchar(100) COLLATE Chinese_PRC_CI_AS,--部门名称
		overTime_date date,--加班日期
		overTime_minute int, --加班时长分钟数
		overTime_beginDatetime varchar(50) COLLATE Chinese_PRC_CI_AS,
		overTime_endDatetime varchar(50) COLLATE Chinese_PRC_CI_AS,
		remark varchar(max) COLLATE Chinese_PRC_CI_AS,
		job_grade varchar(max) COLLATE Chinese_PRC_CI_AS,
		technological_job varchar(max) COLLATE Chinese_PRC_CI_AS

	)
	create table #temp_order_data(
		user_serial bigint,--人员ID
		overTime_minute int, --加班时长分钟数
		ot_begin int,--加班开始时间点
		ot_end int,--加班结束时间点
		overTime_beginDatetime varchar(50) COLLATE Chinese_PRC_CI_AS,
		overTime_endDatetime varchar(50) COLLATE Chinese_PRC_CI_AS,
		remark varchar(max) COLLATE Chinese_PRC_CI_AS
	)


	--错误信息
	create table #return_data(
		user_no varchar(50) COLLATE Chinese_PRC_CI_AS,
		user_name varchar(50) COLLATE Chinese_PRC_CI_AS,
		dep_name varchar(50) COLLATE Chinese_PRC_CI_AS,
		date varchar(50),
		begin_time varchar(50),
		end_time varchar(50),
		error_msg varchar(2000) COLLATE Chinese_PRC_CI_AS
	)
	--1将导入临时表数据放入#temp_data
	insert into #temp_data
		select
			u.user_serial,
			o.user_no,
			u.user_lname,
			d.dep_serial,
			d.dep_name,
			o.date,
			DATEDIFF(mi, o.begin_time, o.end_time),
			o.begin_time,
			o.end_time,
			o.remark,
			u.job_grade,
			u.technological_job
		from bd_temp_import_overTime o
		left join dt_user u on o.user_no = u.user_no
		left join dt_dep d on u.user_dep = d.dep_serial
		where o.serial_number = @serialNumber
	--2校验数据有效性
	--2.1判断人员是否存在
	insert into #return_data
	select user_no,user_name,dep_name,overTime_date,overTime_beginDatetime,overTime_endDatetime,'该人员在系统中不存在'
	from #temp_data
	where user_serial is null;
	--移除不合格的人员
	delete d from #temp_data d INNER JOIN #return_data r on r.user_no = d.user_no

	--2.2判断该人员是否允许加班
	insert into #return_data
	select a.user_no,a.user_name,a.dep_name,a.overTime_date,a.overTime_beginDatetime,a.overTime_endDatetime,'岗位级别限制，无法申请加班单'
	from #temp_data a where a.user_serial in(
		select x.user_serial from (
			select u.user_serial,
						u.job_grade,
						u.technological_job,
						p.overtime_reject_administrative,
						p.overtime_reject_technology
			from #temp_data u ,bd_sys_global_params p
			)x
			where x.overtime_reject_administrative like '%' + case when x.job_grade is null or x.job_grade='' then 'N/A' else x.job_grade end +'%' or
						x.overtime_reject_technology like  '%' + case when x.technological_job is null or x.technological_job='' then 'N/A' else x.technological_job end  +'%'
	)

		--移除不合格的人员
	delete d from #temp_data d INNER JOIN #return_data r on r.user_no = d.user_no

	--2.3判断是否封账
	insert into #return_data
	select a.user_no,a.user_name,a.dep_name,a.overTime_date,a.overTime_beginDatetime,a.overTime_endDatetime,'该时段考勤已经封账,不能加班'
	from #temp_data a
	left join bd_kq_accounts c on a.user_serial = c.user_serial  and year(a.overTime_date) = c.year and month(a.overTime_date) = c.month
	where isnull(c.lock_status,0)>0

	--移除不合格的人员
	delete d from #temp_data d INNER JOIN #return_data r on r.user_no = d.user_no

	--2.4判断加班是否重叠
	insert into #return_data
	select user_no,user_name,dep_name,overTime_date,overTime_beginDatetime,overTime_endDatetime,
	'该时间段存在加班记录,冲突时间段：'+case when a.kssj>convert(datetime,d.overTime_beginDatetime) then convert(varchar(16),a.kssj,120) else d.overTime_beginDatetime end +' - '+
				case when a.jssj<convert(datetime,d.overTime_endDatetime) then convert(varchar(16),a.jssj,120) else d.overTime_endDatetime end
	from lr_jiaba a
	INNER JOIN #temp_data d on a.user_serial = d.user_serial
	INNER JOIN bd_over_time_order o on a.order_id = o.order_id
	where isnull(o.is_delete,0) = 0 and  isnull(a.is_delete,0) = 0 and
				not(a.kssj >= convert(datetime,d.overTime_endDatetime) or a.jssj <= convert(datetime,d.overTime_beginDatetime))

	--移除不合格的人员
	delete d from #temp_data d INNER JOIN #return_data r on r.user_no = d.user_no

	--2遍历调用
	DECLARE deptList CURSOR LOCAL FOR
	SELECT
				u.user_dep
	FROM #temp_data d
	INNER JOIN dt_user u on u.user_no = d.user_no
	group by u.user_dep

	OPEN deptList
	FETCH NEXT FROM deptList INTO @dep_serial
	WHILE @@fetch_status = 0
		BEGIN
			DECLARE rqList CURSOR LOCAL FOR
				SELECT
							o.overTime_date
				FROM #temp_data o
				group by o.overTime_date order by o.overTime_date

				OPEN rqList
				FETCH NEXT FROM rqList INTO @date
				WHILE @@fetch_status = 0
					BEGIN

						--	3根据@dep_serial和@date收集一个单子需要的明细
						insert into #temp_order_data
						select
								user_serial,
								overTime_minute,
								dbo.bd_fn_datetimeToInt(@date, overTime_beginDatetime),
								dbo.bd_fn_datetimeToInt(@date, overTime_endDatetime),
								overTime_beginDatetime,
								overTime_endDatetime,
								remark
							from #temp_data
							where overTime_date = @date and user_dep = @dep_serial;
						--4当#temp_order_data有数据是才生产单子
						select @counts = count(1) from #temp_order_data;
						if @counts>0
							BEGIN
							--生成单号和ID
								exec bd_proc_sys_getMaxNumber '10',@order_no output
								exec bd_proc_sys_getMaxId 10,@order_id output
								--保存单
								insert into bd_over_time_order (
										order_id,
										order_no,
										ot_date,
										sum_user,
										sum_minute,
										status,
										create_user,
										create_time,
										shift_id,
										is_delete,
										apply_type
								)VALUES(
										@order_id,
										@order_no,
										@date,
										@counts,
										(select sum(overTime_minute) from #temp_order_data),
										0,
										@operator,
										getdate(),
										0,
										0,
										2
								);

								--保存加班明细单
								insert into lr_jiaba(
									user_serial,
									bh,
									kssj,
									jssj,
									jsjg,
									gly_no,
									order_id,
									ot_begin,
									ot_end,
									over_time_type_id,
									bz,
									shift_id,
									is_delete,
									jbrq,
									settle_year,
									settle_month
								)
								select
									user_serial,
									0,
									overTime_beginDatetime,
									overTime_endDatetime,
									overTime_minute,
									@operator,
									@order_id,
									ot_begin,
									ot_end,
									0,
									remark,
									0,
									0,
									@date,
									DATEPART(year, dbo.bd_fn_getSettleDate(@date)),
									DATEPART(month, dbo.bd_fn_getSettleDate(@date))
								from #temp_order_data
								--记录id
								set @order_ids = concat(@order_ids,',',@order_id)
							END

					--清除明细单临时表
					TRUNCATE TABLE #temp_order_data
						FETCH NEXT FROM rqList INTO  @date
					END
					CLOSE rqList
					DEALLOCATE rqList

			FETCH NEXT FROM deptList INTO  @dep_serial
		END
	CLOSE deptList
	DEALLOCATE deptList
	--返回数据
	select (
		select user_no as userNo,
					user_name as userName,
					dep_name as depName,
					date,
					begin_time as beginTime,
					end_time as endTime,
					error_msg as errorMsg
			from #return_data for json path) as failData,
		SUBSTRING(@order_ids, 2, len(@order_ids)) as ids
END
go
